package fr.esisar.picaso.requetes;
//Tentative de commit
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import fr.esisar.picaso.entites.Format;
import fr.esisar.picaso.moteur.Connexion;
import java.util.ArrayList;

public class RequetesFormat {

	
	public Format getFormat(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT * FROM U0.Format WHERE idFormat="+id;
		ResultSet rs = st.executeQuery(requete);
		
		Format format = null;
		while(rs.next()) {
			format = new Format(id, rs.getFloat("prix"), rs.getString("designation"), rs.getInt("longueur"), rs.getInt("largeur"));
		}
		
		return format;
	}
	
	public float getPrix(int id) throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT prix FROM U0.Format WHERE idFormat='"+id+"';";
		ResultSet rs = st.executeQuery(requete);
		
		float prix=0;
		while(rs.next()) {
			prix = rs.getFloat("prix");
		}
		return prix;
	}
	
	public String getType(int id) throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT type FROM U0.Format WHERE idFormat='"+id+"';";
		ResultSet rs = st.executeQuery(requete);
		
		String type = "";
		while(rs.next()) {
			type = rs.getString("type");
		}
		return type;
	}
	
	public int getDimensions(int id) throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT dimensions FROM U0.Format WHERE idFormat='"+id;
		ResultSet rs = st.executeQuery(requete);
		
		int type = 0;
		while(rs.next()) {
			type = rs.getInt("dimensions");
		}
		return type;
	}
	
	public void setFormat(int id, float prix, String type, String dimensions) throws SQLException {
            Connection connexion = Connexion.getConnection();
            Statement st = connexion.createStatement();

            String requete = "UPDATE U0.Format SET prix='"+prix+"', type='"+type+"', dimensions='"+dimensions+"' WHERE idFormat="+id;
            st.executeQuery(requete);
            connexion.commit();
	}
	
	public void setPrix(int id, float prix) throws SQLException {
            Connection connexion = Connexion.getConnection();
            Statement st = connexion.createStatement();

            String requete = "UPDATE U0.Format SET prix='"+prix+"'WHERE idFormat="+id;
            st.executeQuery(requete);
            connexion.commit();
	}
	
	public void setType(int id, String type) throws SQLException {
            Connection connexion = Connexion.getConnection();
            Statement st = connexion.createStatement();

            String requete = "UPDATE U0.Format SET type='"+type+"' WHERE idFormat="+id;
            st.executeQuery(requete);
            connexion.commit();
	}
	
	public void setDimensions(int id, String dimensions) throws SQLException {
            Connection connexion = Connexion.getConnection();
            Statement st = connexion.createStatement();

            String requete = "UPDATE U0.Format SET dimensions='"+dimensions+"' WHERE idFormat="+id;
            st.executeQuery(requete);
            connexion.commit();
	}
        
        public ArrayList<String> getListeFormat()throws SQLException{
            
            ArrayList<String> listeFormat = new ArrayList<String>();
            
            Connection connexion = Connexion.getConnection();
            Statement st = connexion.createStatement();

            String requete = "SELECT DISTINCT designation FROM U0.Format";
            ResultSet rs = st.executeQuery(requete);
            
            while(rs.next()) {
                    listeFormat.add(rs.getString("designation"));
            }
            return listeFormat;
        }
}
